﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace BanHangCshap.Class
{
    class Quy:Database
    {
        private int _maquy;
        

        public int maquy
        {
            set { _maquy = value; }
            get { return _maquy; }
        }
        private string _tenquy;
        public string tenquy
        {
            set { _tenquy = value; }
            get { return _tenquy; }
        }
        private double _tienquybandau;
        public double tienquybandau
        {
            set { _tienquybandau = value; }
            get { return _tienquybandau; }
        }
        public Quy()
        {
            this.maquy = 0;
            this.tenquy = "";
            
        }
        public Quy(int maquy)
        {
            string sql = "select * from Quy where maquy = " + maquy + "";
            foreach (DataRow dr in getDataTable(sql).Rows)
            {
                this.maquy = maquy;
                this.tenquy = dr["tenquy"].ToString().Trim();
                this.tienquybandau = double.Parse(dr["tienquybandau"].ToString());
            }

        }
        public Boolean isExistQuy(int maquy)
        {
            string sql = "select * from Quy where maquy = " + maquy +  "";
            return isExist(sql);
        }
        public Boolean suaQuy(int maquy)
        {
            string sql = "update Quy set tenquy = N'" + tenquy + "',tienquybandau = " + tienquybandau + " where maquy = " + maquy + "";
            return executeQuery(sql);
        }
        public Boolean themQuy()
        {
            string sql = "insert Quy(tenquy,tienquybandau) values(N'" + tenquy + "'," + tienquybandau + ")";
            return executeQuery(sql);
        }
        public Boolean xoaQuy(int maquy)
        {
            string sql = "delete Quy where maquy = " + maquy + "";
            return executeQuery(sql);
        }
        public DataTable layDSQuy()
        {
            string sql = "select * from Quy";
            return getDataTable(sql);
        }
        
        public DataTable baoCaoTonQuyChiTiet(string thongtindoanhnghiep, string tungay, string denngay)
        {
            /*string sql = "select distinct N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian, ";
            sql += "ngay,chungtu,maquy,tenquy,noidung,sodudauki,thu,chi,soducuoiki,sort,loai ";
            sql += "from  ";
            sql += "( ";
            sql += "select distinct '' as sort,'sodk' as loai,null as chungtu,null as ngay, ";
            sql += "N'Số dư đầu kỳ' as noidung,maquy,tenquy,(select tonquy from v_ThuChiTonQuy where maquy = a.maquy and sort = (select distinct max(sort) from v_ThuChiTonQuy where maquy = a.maquy and convert(Datetime,ngaythuchi,103) < convert(Datetime,'" + tungay + "',103) )) as sodudauki, null as thu, null as chi, null as soducuoiki ";
            sql += "from Quy a ";
            sql += "union all ";
            sql += "select distinct '40000101-001' as sort,'sodk'  as loai,null as chungtu,null as ngay, ";
            sql += "N'Số dư cuối kỳ' as noidung,maquy,tenquy,null as sodudauki, null as thu, null as chi,(select distinct tonquy from v_ThuChiTonQuy where maquy = a.maquy and sort = (select distinct max(sort) from v_ThuChiTonQuy where maquy = a.maquy and convert(Datetime,ngaythuchi,103) <= convert(Datetime,'" + denngay + "',103) )) as soducuoiki ";
            sql += "from Quy a ";
            sql += "UNION ALL ";
            sql += "select distinct  sort, loai,mathuchi as chungtu,ngaythuchi as ngay, ";
            sql += "tennoidungthuchi as  noidung,maquy,tenquy,null as sodudauki,  thu,  chi,null as soducuoiki ";
            sql += "from v_ThuChiTonQuy where convert(Datetime,ngaythuchi,103) >= convert(Datetime,'" + tungay + "',103) and convert(Datetime,ngaythuchi,103) <= convert(Datetime,'" + denngay + "',103) ";
            sql += ") temp  order by maquy,sort";*/
            string sql = "select distinct N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,N'Từ ngày: ' + '" + tungay + "' + N' đến ngày: ' + '" + denngay + "' as khoangthoigian, ";
            sql += "ngay,chungtu,maquy,tenquy,noidung,sodudauki,thu,chi,soducuoiki,sort,loai ";
            sql += "from  ";
            sql += "( ";
            sql += "select distinct '' as sort,0 as loai,null as chungtu,null as ngay, ";
            sql += "N'Số dư đầu kỳ' as noidung,maquy,tenquy,(select tonquy from v_ThuChiTonQuy where maquy = a.maquy and sort = (select distinct max(sort) from v_ThuChiTonQuy where maquy = a.maquy and convert(Datetime,ngaythuchi,103) < convert(Datetime,'" + tungay + "',103) )) as sodudauki, null as thu, null as chi, null as soducuoiki ";
            sql += "from Quy a ";
            sql += "union all ";
            sql += "select distinct '40000101-001' as sort,0  as loai,null as chungtu,null as ngay, ";
            sql += "N'Số dư cuối kỳ' as noidung,maquy,tenquy,null as sodudauki, null as thu, null as chi,(select distinct tonquy from v_ThuChiTonQuy where maquy = a.maquy and sort = (select distinct max(sort) from v_ThuChiTonQuy where maquy = a.maquy and convert(Datetime,ngaythuchi,103) <= convert(Datetime,'" + denngay + "',103) )) as soducuoiki ";
            sql += "from Quy a ";
            sql += "UNION ALL ";
            sql += "select distinct  sort, loai,mathuchi as chungtu,ngaythuchi as ngay, ";
            sql += "tennoidungthuchi as  noidung,maquy,tenquy,null as sodudauki,  thu,  chi,null as soducuoiki ";
            sql += "from v_ThuChiTonQuy where convert(Datetime,ngaythuchi,103) >= convert(Datetime,'" + tungay + "',103) and convert(Datetime,ngaythuchi,103) <= convert(Datetime,'" + denngay + "',103) ";
            sql += ") temp  order by maquy,sort";
            return getDataTable(sql);
        }
    }
}
